TD révision SQL
Langage de manipulation de données (LMD)
L3 MIASHS |
| Année 2025 |
Exercices de révision en SQL
La répartition des exercices n’est pas représentative de l’examen.
Solutions publiées le 4 janvier !
Exercice 1 : schéma world
Quels sont les pays dont plus de \(1.000.000\) d’habitants parlent une langue non officielle ? (72 lignes)
SELECT DISTINCT name_country
FROM country JOIN countrylanguage USING(countrycode)
WHERE (NOT isofficial) AND
(percentage / 100) * population_country >= 1000000 ;Quels est le nombre de villes par region ?
SELECT co.region , COUNT(ci.id) AS nbr_city
FROM country co JOIN city ci USING(countrycode)
GROUP BY co.region;Quelles sont les noms de villes qui désignent au moins deux villes distinctes ? (65 lignes)
SELECT DISTINCT ci1.name
FROM city ci1 JOIN city ci2
ON ci1.name = ci2.name AND ci1.id <> ci2.id;Quelles sont les langues parlées à la fois dans les continents nord-américain et sud-américain ? (10 lignes)
SELECT DISTINCT cl1.language
FROM country c1 JOIN countrylanguage cl1 ON c1.countrycode = cl1.countrycode
JOIN countrylanguage cl2 ON cl1.language = cl2.language
JOIN country c2 ON cl2.countrycode = c2.countrycode
WHERE c1.continent ilike 'south am%' and c2.continent ilike 'north am%';Quels sont les pays qui ont au moins \(3\) langues officielles ? (8 lignes)
SELECT c.name_country
FROM country c JOIN countrylanguage cl USING (countrycode)
WHERE cl.isofficial
GROUP BY c.name_country
HAVING COUNT(*) > 2 ;Quel est le pays qui a le plus de villes recensées dans la table city? Combien de villes ? (363 villes)
WITH country_city AS
(SELECT co.countrycode, co.name_country , COUNT(ci.name) nbrcity
FROM country co JOIN city ci USING (countrycode)
GROUP BY co.countrycode, co.name_country)
SELECT countrycode, name_country, nbrcity
FROM country_city
WHERE nbrcity =
(SELECT MAX(nbrcity)
FROM country_city) ;Quels sont les pays pour lesquels une langue non officielle est parlée par strictement plus de monde (dans le pays) qu’il n’y a d’habitants dans la capitale ? (118 lignes)
SELECT DISTINCT co.name_country
FROM country co
JOIN city ci ON ci.id = co.capital
JOIN countrylanguage cl ON co.countrycode = cl.countrycode
WHERE NOT cl.isofficial
AND percentage / 100 * co.population_country > ci.population ;Exercice 2 : schéma pagila
Quel est le prix moyen d’une location de DVD ?
SELECT AVG(amount)
FROM payment ;Quels sont les clients qui ont loué dans une autre ville que celle dans laquelle ils vivent ? Afficher l’identifiant du client, son nom, son prénom, les noms de la ville de résidence et de la ville de location. (1198 lignes distinctes)
WITH
c_home AS
(SELECT customer_id, last_name, first_name, city_id
FROM customer JOIN address USING (address_id)
),
c_rental AS
(SELECT r.customer_id, a.city_id
FROM rental r JOIN inventory i USING (inventory_id)
JOIN store s ON i.store_id = s.store_id
JOIN address a ON s.address_id = a.address_id
)
SELECT DISTINCT ch.customer_id, ch.last_name, ch.first_name, ci1.city AS city_home, ci2.city AS city_rental
FROM city ci1 JOIN c_home ch ON ci1.city_id = ch.city_id
JOIN c_rental cr USING (customer_id)
JOIN city ci2 ON cr.city_id = ci2.city_id
WHERE ch.city_id <> cr.city_id
ORDER BY ch.last_name, ch.first_name;Quel est l’acteur présent dans le plus de films ? (GINA DEGENERES, 42 films)
WITH actor_film AS
(SELECT actor_id, first_name, last_name, COUNT(DISTINCT film_id) nbrfilms
FROM actor JOIN film_actor USING (actor_id)
GROUP BY actor_id
)
SELECT *
FROM actor_film
WHERE nbrfilms = (SELECT MAX(nbrfilms) FROM actor_film) ;Quel est le film (id et nom) qui a rapporté le plus d’argent ? (879, TELEGRAPH VOYAGE)
WITH film_amount AS
(SELECT film_id , SUM(amount) AS dollar
FROM inventory
JOIN rental USING (inventory_id)
JOIN payment USING (rental_id)
GROUP BY film_id
)
SELECT film_id , title, dollar
FROM film_amount JOIN film USING (film_id)
WHERE dollar >= ALL (SELECT dollar FROM film_amount) ;